package o2o.web.actions.statis;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.Gson;
import com.jinyou.utils.common.*;
import com.mongodb.BasicDBObject;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import map.HttpRequest;
import o2o.cache.system.SysSettingMobileMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.cache.wallet.UserWalletMemory;
import o2o.config.dao.MongoDaoImpl;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.sys.SysSettingKey;
import o2o.dao.PO.user.USER_COMPANY_STATE;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.PO.wallet.UserWallet;
import o2o.dao.TO.integral.DataUserIntegral;
import o2o.dao.TO.integral.UserIntegralTO;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.system.data.SYS_SETTINGS;
import o2o.web.actions.user.User;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

import static com.jinyou.utils.common.AjaxResult.successResult;

/**
 * @Description: TODO
 * Created with IntelliJ IDEA.
 * User: Jahnke [1029777564@qq.com]
 * Date: 2019-02-20 21:09
 * To change this template use File | Settings | File Templates.
 */
@Controller
public class UserRecordsStatisController {

    /**
     * 获取用户记录-统计信息
     *
     * @param token
     * @param orderBy   1-累计充值  2-累计消费   3-当前余额  4-累计订单  5-累计退单  6-累计积分  7-当前积分 8-用户创建时间  9 最近下单时间
     * @param orderType 1-由高到低   2-由低到高
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "admin/user/records/statistics")
    @ResponseBody
    public String userRecordStatis(String token, String province, String county, String city, Long startTime, Long endTime, Integer orderBy, Integer orderType, Integer page, Integer size, Integer isExport) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN) {
            return successResult(null, 0);
        }
        if (startTime == null || startTime.equals("")) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null || startTime.equals("")) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        String orderStr = "";
        if (orderBy == null || orderBy == 1 || orderBy == 6 || orderBy == 7) {
            orderStr = "charges";
        } else if (orderBy == 2) {
            orderStr = "prices";
        } else if (orderBy == 3) {
            orderStr = "money";
        } else if (orderBy == 4) {
            orderStr = "orderCounts";
        } else if (orderBy == 5) {
            orderStr = "refundOrderCounts";
        } else if (orderBy == 8) {
            orderStr = "createTime";
        } else if (orderBy == 9) {
            orderStr = "lastOrderTime";
        }
        boolean isDown = false;
        if (isExport != null && isExport == 1) {
            isDown = true;
        }

        if (ValidateUtil.isNotNull(city) && null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {
            if (JYPinyinHelper.isChinese(city)) {
                city = JYPinyinHelper.toHanyuPinyin(city);
            }
        }

        /*
            SELECT ufo.username,uw.money,
                SUM(IFNULL(ori.totalPrice,0)) charges,SUM(CASE WHEN ori2.orderstatus=9 THEN IFNULL(ori2.totalPrice,0) ELSE 0 END) prices,
                SUM(CASE WHEN ori2.orderstatus=9 THEN 1 ELSE 0 END) orderCounts,SUM(CASE WHEN ori2.isRefundApply=2 THEN 1 ELSE 0 END) refundOrderCounts,
                SUM(CASE WHEN ori2.isRefundApply=2 THEN ori2.totalPrice ELSE 0 END) refundOrderMoney
            FROM user_info AS ufo
            LEFT JOIN order_info AS ori ON ori.username = ufo.username AND ori.ordertype=5 AND ori.orderstatus=9
            LEFT JOIN order_info AS ori2 ON ori2.username = ufo.username AND ori2.ordertype!=5
            LEFT JOIN user_wallet AS uw ON uw.username=ufo.username
            GROUP BY username
         */
        String sql = "SELECT ufo.username,uw.money,ufo.createTime,ufo.companyUserState, ori3.createTime as lastOrderTime, ori3.address, " +
                " SUM(IFNULL(ori.totalPrice,0)) charges,SUM(CASE WHEN ori2.orderstatus=9 THEN IFNULL(ori2.totalPrice,0) ELSE 0 END) prices, " +
                " SUM(CASE WHEN ori2.orderstatus=9 THEN 1 ELSE 0 END) orderCounts,SUM(CASE WHEN ori2.isRefundApply=2 THEN 1 ELSE 0 END) refundOrderCounts, " +
                " SUM(CASE WHEN ori2.isRefundApply=2 THEN ori2.totalPrice ELSE 0 END) refundOrderMoney " +
                " FROM user_info AS ufo " +
                " LEFT JOIN order_info AS ori ON ori.username = ufo.username AND ori.ordertype=5 AND ori.orderstatus=9  " + " AND ori.createTime > " + startTime + " AND  ori.createTime <" + endTime +
                " LEFT JOIN order_info AS ori2 ON ori2.username = ufo.username AND ori2.ordertype!=5  " + " AND ori2.createTime > " + startTime + " AND  ori2.createTime <" + endTime +
                " LEFT JOIN (SELECT " +
                "      ori6.`createTime`," +
                "      ori6.username," +
                "      ori6.`address2`," +
                "      ori6.`address` " +
                "    FROM " +
                "      order_info AS ori6 " +
                "      INNER JOIN  " +
                "        (SELECT " +
                "          username, " +
                "          MAX(createTimE) createTime  " +
                "        FROM " +
                "          order_info  WHERE ordertype != 5 AND orderstatus = 9 " + " AND createTime > " + startTime + " AND createTime < " + endTime +
                "        GROUP BY username) AS ori5  " +
                "        ON ori6.`username` = ori5.username " +
                "        AND ori6.`createTime` = ori5.createTime ) AS ori3 " +
                "    ON ori3.username = ufo.username " +
                " LEFT JOIN user_wallet AS uw ON uw.username=ufo.username " +
                " WHERE ufo.userType=" + UserType.USER +" AND ufo.delFlag = 0 "+
//                (StringUtils.isNotEmpty(province) ? (" AND ufo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND ufo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND ufo.county='" + county + "'") : "") +
//                " AND ufo.createTime BETWEEN " + startTime + " AND " + endTime +
                " GROUP BY username " +
                " ORDER BY " + orderStr + (orderType == null || orderType == 1 ? " DESC" : " ASC");
        if (!isDown) {
            sql = sql + " LIMIT " + ((page - 1) * size) + "," + size;
        }
//        +
//                " LIMIT " + ((page - 1) * size) + "," + size;
        String sqlCount = "SELECT COUNT(*) COUNTS from (SELECT ufo.username,uw.money,ufo.createTime, " +
                " SUM(IFNULL(ori.totalPrice,0)) charges,SUM(CASE WHEN ori2.orderstatus=9 THEN IFNULL(ori2.totalPrice,0) ELSE 0 END) prices, " +
                " SUM(CASE WHEN ori2.orderstatus=9 THEN 1 ELSE 0 END) orderCounts,SUM(CASE WHEN ori2.isRefundApply=2 THEN 1 ELSE 0 END) refundOrderCounts, " +
                " SUM(CASE WHEN ori2.isRefundApply=2 THEN ori2.totalPrice ELSE 0 END) refundOrderMoney " +
                " FROM user_info AS ufo " +
                " LEFT JOIN order_info AS ori ON ori.username = ufo.username AND ori.ordertype=5 AND ori.orderstatus=9  " +
                " LEFT JOIN order_info AS ori2 ON ori2.username = ufo.username AND ori2.ordertype!=5  " +
                " LEFT JOIN user_wallet AS uw ON uw.username=ufo.username " +
                " WHERE ufo.userType=" + UserType.USER +
                (StringUtils.isNotEmpty(province) ? (" AND ufo.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND ufo.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND ufo.county='" + county + "'") : "") +
//                " AND ufo.createTime BETWEEN " + startTime + " AND " + endTime +
                " GROUP BY username " +
                " ORDER BY " + orderStr + (orderType == null || orderType == 1 ? " DESC" : " ASC")
                + ")a";

        long total = 0L;
        List<HashMap<String, Object>> list = null;
        try {
            total = UserDaoSql.getInstance().getCount(sqlCount);
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        Set<String> usernames = null;
        if (list != null && !list.isEmpty()) {
            usernames = new HashSet<>();
            for (int i = 0; i < list.size(); i++) {
                // 添加配送员信息
                String username = list.get(i).get("username").toString();
                UserWallet userWallet = UserWalletMemory.getInstance().getWallet(username);
                UserInfo userInfo = UserInfoMemory.getInstance().getUserInfo(username);
                list.get(i).put("wallet", userWallet == null ? 0D : userWallet.getMoney());
                list.get(i).put("name", userInfo == null ? "" : userInfo.getName());
                list.get(i).put("icon", userInfo == null ? "" : (userInfo != null && StringUtils.isNotEmpty(userInfo.getSignPhoto()) && !userInfo.getSignPhoto().startsWith("http") ? SYS_CONFIG_PATH.IMAGE_BASE_URL + userInfo.getSignPhoto() : userInfo.getSignPhoto()));

                list.get(i).put("score", 0);
                list.get(i).put("totalScore", 0);
                usernames.add(username);
            }
        }
        // mongo查詢用戶的最後一次登陸時間
        Map<String, Long> userLastLogin = null;
        if (usernames != null) {
            BasicDBObject query = new BasicDBObject();
            query.append("username", new BasicDBObject("$in", usernames));
            List<HashMap<String, Object>> lll = MongoDaoImpl.getInstance().find("records_user", "user_login_curr", query, 100);
            userLastLogin = lll == null ? null :
                    lll.stream().collect(Collectors.toMap(a -> a.get("username").toString(), a -> Long.parseLong(a.get("loginDate").toString())));
        }
        if (userLastLogin != null && list != null) {
            for (int i = 0; i < list.size(); i++) {
                String username = list.get(i).get("username").toString();
                list.get(i).put("lastLogin", userLastLogin.containsKey(username) ? userLastLogin.get(username) : null);
            }
        }
        //  导出
        if (null != isExport && isExport - 1 == 0) {
            String downloadUrl = exportExcel(list);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                return AjaxResult.successResult(url);
            }
        }

        return successResult(list, total);
    }


    @RequestMapping(method = RequestMethod.POST, value = "admin/user/records/pop/wallet")
    @ResponseBody
    public String popWallet(String token, Integer page, Integer size) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }
        String sql = "SELECT id,money,channel,popType,alipay,alipayRealName,tenpay,tenpayRealName,bankUserName," +
                "bankName,bankCard,username,isDone,createTime " +
                "FROM user_wallet_pop " +
                "WHERE " +
                (user.getUserType() == UserType.ADMIN ? (" username='" + user.getUsername() + "'") : "");
        List<HashMap<String, Object>> lists = null;
        try {
            lists = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        lists = (List<HashMap<String, Object>>) ToolUtils.list(lists, (page - 1) * size + 1, page * size);
        JSONObject obj = new JSONObject();
        obj.put("status", 1);
        obj.put("data", JSONArray.toJSON(lists == null ? Collections.emptyList() : lists));
        return obj.toString();
    }

    @RequestMapping(method = RequestMethod.POST, value = "admin/user/count/city/statistics")
    @ResponseBody
    public String userCountStatis_city(String token) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }
        String sql = "SELECT city,COUNT(1) counts FROM user_info WHERE delFlag=0 " +
                "AND city != '' AND city != 'null'" +
                "GROUP BY city " +
                "ORDER BY counts DESC";
        List<HashMap<String, Object>> list = null;
        try {
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (ValidateUtil.isAbsList(list)) {
            return AjaxResult.successResult(list.size(), list);
        }
        return AjaxResult.successResult(0, null);
    }

    @RequestMapping(method = RequestMethod.POST, value = "admin/user/count/statistics")
    @ResponseBody
    public String userCountStatis(String token) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.errorResult("您没有操作权限");
        }
        Map<String, Long> map = new HashMap<>();
        String userSql = "select count(*) as counts from " + PojoTables.UserInfo + " where delFlag = 0";
        Long userCount = null;
        try {
            userCount = UserDaoSql.getInstance().getCount(userSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        map.put("userCount", userCount);
        // 店铺
        String shopSql = "select count(*) as counts from " + PojoTables.ShopInfo + " where delFlag = 0 ";
        Long shopCount = null;
        try {
            shopCount = UserDaoSql.getInstance().getCount(shopSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        map.put("shopCount", shopCount);

        String cityAdminSql = "SELECT  " +
                "   SUM(CASE WHEN sys.level=4 THEN 1 ELSE 0 END) as provinceAdminCount,  " +  // 省代理
                "    SUM(CASE WHEN sys.level=3 THEN 1 ELSE 0 END) as cityAdminCount,  " +      // 市代理
                "     SUM(CASE WHEN sys.level=2 THEN 1 ELSE 0 END) as countyAdminCount,  " +    // 区县代理
                "      SUM(CASE WHEN sys.level=1 THEN 1 ELSE 0 END) as townAdminCount,  " +      // 镇代理
                "      SUM(1) as totalAdminCount  " +      // 总代理
                " FROM sys_city_admin AS sys WHERE delFlag = 0";

        HashMap<String, Object> cityMap = null;
        try {
            cityMap = UserDaoSql.getInstance().queryMapBySql(cityAdminSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (cityMap != null) {
            map.put("provinceAdminCount", Long.parseLong(cityMap.get("provinceAdminCount") == null ? "0" : cityMap.get("provinceAdminCount").toString()));
            map.put("cityAdminCount", Long.parseLong(cityMap.get("cityAdminCount") == null ? "0" : cityMap.get("cityAdminCount").toString()));
            map.put("countyAdminCount", Long.parseLong(cityMap.get("countyAdminCount") == null ? "0" : cityMap.get("countyAdminCount").toString()));
            map.put("townAdminCount", Long.parseLong(cityMap.get("townAdminCount") == null ? "0" : cityMap.get("townAdminCount").toString()));
            map.put("totalAdminCount", Long.parseLong(cityMap.get("totalAdminCount") == null ? "0" : cityMap.get("totalAdminCount").toString()));
        }
        // 骑手的
        String postmanStr = "select count(*) as counts from " + PojoTables.CityPostman + " where delFlag = 0";
        Long postmanCount = null;
        try {
            postmanCount = UserDaoSql.getInstance().getCount(postmanStr);
        } catch (Exception e) {
            e.printStackTrace();
        }
        map.put("postmanCount", postmanCount);
        return AjaxResult.successResult(map);
    }

    // ===================
    // 导excel
    private String exportExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        Integer hasWallet = SysSettingMobileMemory.getInstance().getValueByCode(SysSettingKey.HAS_WALLET);
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);
            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式

            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "账号"));
            sheet.addCell(new Label(col++, row, "姓名"));

            sheet.addCell(new Label(col++, row, "订单数量"));
            if (hasWallet == 1) {
                sheet.addCell(new Label(col++, row, "余额"));
                sheet.addCell(new Label(col++, row, "累计充值"));
            }
//            sheet.addCell(new Label(col++, row, "余额"));
//            sheet.addCell(new Label(col++, row, "累计充值"));
            sheet.addCell(new Label(col++, row, "累计消费"));
            sheet.addCell(new Label(col++, row, "退单数量"));
            sheet.addCell(new Label(col++, row, "退单金额"));
//            sheet.addCell(new Label(col++, row, "当前积分"));
//            sheet.addCell(new Label(col++, row, "历史积分"));
            sheet.addCell(new Label(col++, row, "注册日期"));
            sheet.addCell(new Label(col++, row, "最后登录日期"));

            row = 1;
            for (HashMap<String, Object> map : list) {
                col = 0;
                sheet.addCell(new Label(col++, row, map.get("username") + ""));
                String name = map.get("name") + "";
                Object companyUserState = map.get("companyUserState");
                if (companyUserState != null && (USER_COMPANY_STATE.STATE_COMPANY_PASS + "").equals(companyUserState + "")) {
                    name = (ValidateUtil.isNull(name) ? "" : name) + "(企业用户)";
                }
                sheet.addCell(new Label(col++, row, name));
                sheet.addCell(new Label(col++, row, map.get("orderCounts") + ""));
                if (hasWallet == 1) {
                    sheet.addCell(new Label(col++, row, map.get("wallet") + ""));
                    sheet.addCell(new Label(col++, row, map.get("charges") + ""));
                }
//                sheet.addCell(new Label(col++, row, map.get("wallet") + ""));
//                sheet.addCell(new Label(col++, row, map.get("charges") + ""));
                sheet.addCell(new Label(col++, row, map.get("prices") + ""));
                sheet.addCell(new Label(col++, row, map.get("refundOrderCounts") + ""));
                sheet.addCell(new Label(col++, row, map.get("refundOrderMoney") + ""));
//                sheet.addCell(new Label(col++, row, map.get("score") + ""));
//                sheet.addCell(new Label(col++, row, map.get("totalScore") + ""));
                String createTime = (map.get("createTime") == null || StringUtils.isEmpty(map.get("createTime").toString())) ? "" : DateUtils.sdfDate.get().format(new Date(Long.parseLong(map.get("createTime").toString())));
                sheet.addCell(new Label(col++, row, createTime));
                String lastLogin = (map.get("lastLogin") == null || StringUtils.isEmpty(map.get("lastLogin").toString())) ? "" : DateUtils.sdfDate.get().format(new Date(Long.parseLong(map.get("lastLogin").toString())));
                sheet.addCell(new Label(col++, row, lastLogin));
//                sheet.addCell(new Label(col++, row, map.get("createTime") + ""));
//                sheet.addCell(new Label(col++, row, map.get("lastLogin") + ""));
                row++;     // 行
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }

    /**
     * 获取用户数据记录
     *
     * @param token
     * @param params
     * @param orderBy   排序
     * @param orderType 1-由高到低   2-由低到高
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "admin/user/records/data/statistics")
    @ResponseBody
    public String userdataStatis(String token, String params, String province, String county, String city, Long startTime, Long endTime,
                                 String orderBy, Integer orderType, Integer page, Integer size, Integer isExport) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN) {
            return successResult(null, 0);
        }
        if (startTime == null || startTime.equals("")) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null || startTime.equals("")) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        /*
            SELECT ui.*,ui.username,IFNULL(uw.money,0) money,IFNULL(uwf.money,0) rechargeMoney,COUNT(ofo.id) orderCount,
                SUM(CASE WHEN ofo.orderType != 5 THEN opy.totalprice ELSE 0 END) totalPrice,
                COUNT(ofo2.id) refundCount
            FROM user_info AS ui
            LEFT JOIN user_wallet uw ON uw.username = ui.username AND uw.delFlag = 0
            LEFT JOIN user_wallet_flow uwf ON uwf.username = ui.username AND uwf.delFlag = 0 AND uwf.orderType = 2 AND uwf.flowType = 1
            LEFT JOIN order_info AS ofo ON ofo.username = ui.username AND ofo.delFlag = 0 AND ofo.orderStatus = 9 AND ofo.isRefundApply = 0 AND ofo.isPartCancelApply = 0
            LEFT JOIN order_pay AS opy ON opy.orderNo=ofo.orderNo
            LEFT JOIN order_info AS ofo2 ON ofo.username = ui.username AND ofo.delFlag = 0 AND (ofo.isRefundApply = 2 OR ofo.isPartCancelApply = 2)
            GROUP BY ui.username
         */
        String sql = "SELECT ui.*,IFNULL(uw.money,0) money,IFNULL(uwf.money,0) rechargeMoney,COUNT(ofo.id) orderCount, " +
                "                SUM(CASE WHEN ofo.orderType != 5 THEN opy.totalprice ELSE 0 END) totalPrice, " +
                "                COUNT(ofo2.id) refundCount " +
                "            FROM user_info AS ui " +
                "            LEFT JOIN user_wallet uw ON uw.username = ui.username AND uw.delFlag = 0 " +
                "            LEFT JOIN user_wallet_flow uwf ON uwf.username = ui.username AND uwf.delFlag = 0 AND uwf.orderType = 2 AND uwf.flowType = 1 " +
                "            LEFT JOIN order_info AS ofo ON ofo.username = ui.username AND ofo.delFlag = 0 AND ofo.orderStatus = 9 AND ofo.isRefundApply = 0 AND ofo.isPartCancelApply = 0 " +
                "            LEFT JOIN order_pay AS opy ON opy.orderNo=ofo.orderNo " +
                "            LEFT JOIN order_info AS ofo2 ON ofo.username = ui.username AND ofo.delFlag = 0 AND (ofo.isRefundApply = 2 OR ofo.isPartCancelApply = 2) " +
                (StringUtils.isNotEmpty(province) ? (" AND ui.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND ui.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND ui.county='" + county + "'") : "") +
//                " AND ui.createTime BETWEEN " + startTime + " AND " + endTime +
                "            GROUP BY ui.username" +
                " ORDER BY " + (StringUtils.isNotEmpty(orderBy) ? orderBy : "orderCount ") + "" +
                (orderType == null || orderType == 1 ? " DESC" : " ASC") +
                " LIMIT " + ((page - 1) * size) + "," + size;

        String sqlCount = " select count(1) counts from (SELECT count(*) " +
                "            FROM user_info AS ui " +
                "            LEFT JOIN user_wallet uw ON uw.username = ui.username AND uw.delFlag = 0 " +
                "            LEFT JOIN user_wallet_flow uwf ON uwf.username = ui.username AND uwf.delFlag = 0 AND uwf.orderType = 2 AND uwf.flowType = 1 " +
                "            LEFT JOIN order_info AS ofo ON ofo.username = ui.username AND ofo.delFlag = 0 AND ofo.orderStatus = 9 AND ofo.isRefundApply = 0 AND ofo.isPartCancelApply = 0 " +
                "            LEFT JOIN order_pay AS opy ON opy.orderNo=ofo.orderNo " +
                "            LEFT JOIN order_info AS ofo2 ON ofo.username = ui.username AND ofo.delFlag = 0 AND (ofo.isRefundApply = 2 OR ofo.isPartCancelApply = 2) " +
                (StringUtils.isNotEmpty(province) ? (" AND ui.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND ui.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND ui.county='" + county + "'") : "") +
//                " AND ui.createTime BETWEEN " + startTime + " AND " + endTime +
                "            GROUP BY ui.username) a";

        long total = 0L;
        List<HashMap<String, Object>> list = null;
        try {
            total = UserDaoSql.getInstance().getCount(sqlCount);
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        Set<String> usernames = null;
        if (list != null && !list.isEmpty()) {
//            usernames = new HashSet<>();
//            for (int i = 0; i < list.size(); i++) {
//                // 添加用户
//                String username = list.get(i).get("username").toString();
//                list.get(i).put("icon", list.get(i).get("signPhoto").toString());
//
//                list.get(i).put("totalIntegral", 0);
//                list.get(i).put("integral", 0);
//                usernames.add(username);
//            }
////            if (null != SYS_SETTINGS.IS_OPEN_DISTRIBUTION && 0 == 1 - SYS_SETTINGS.IS_OPEN_DISTRIBUTION) {
//            try {
//                String requrstStr = HttpRequest.sendPost(SYS_CONFIG_PATH.FEN_XIAO_SERVICE_URL + "/api/distribute/integral/records/list/by_usernames"
//                        , "usernames=" + StringUtils.join(usernames, ",") + "&sysCustomer=" + SYS_CONFIG_PATH.FEN_XIAO_SERVICE_KEY);
//
//                DataUserIntegral dataUserIntegral= new Gson().fromJson(requrstStr, DataUserIntegral.class);
//                if (null != dataUserIntegral) {
//                    List<UserIntegralTO> data = dataUserIntegral.getData();
//
//                }
//            } catch (Exception e) {
//                e.printStackTrace();
//                System.out.println("分销处理失败");
//            }
//            }
            usernames = list.stream().filter(a -> a.get("username") != null).map(a -> a.get("username").toString()).collect(Collectors.toSet());
            Map<String, UserIntegralTO> userIntegralTOMap = null;
            // 积分
            if (usernames != null) {
                try {
                    String requrstStr = HttpRequest.sendPost(SYS_CONFIG_PATH.FEN_XIAO_SERVICE_URL + "/api/distribute/integral/records/list/by_usernames"
                            , "usernames=" + StringUtils.join(usernames, ",") + "&sysCustomer=" + SYS_CONFIG_PATH.FEN_XIAO_SERVICE_KEY);

                    DataUserIntegral dataUserIntegral = new Gson().fromJson(requrstStr, DataUserIntegral.class);
                    if (null != dataUserIntegral) {
                        List<UserIntegralTO> data = dataUserIntegral.getData();
                        if (data != null && !data.isEmpty()) {
                            userIntegralTOMap = data.stream().collect(Collectors.toMap(UserIntegralTO::getUsername, Function.identity()));
                        }

                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println("积分获取失败" + e.getMessage());
                    //
                }
            }

            // mongo查詢用戶的最後一次登陸時間
            Map<String, Long> userLastLogin = null;
            if (usernames != null) {
                BasicDBObject query = new BasicDBObject();
                query.append("username", new BasicDBObject("$in", usernames));
                List<HashMap<String, Object>> lll = MongoDaoImpl.getInstance().find("records_user", "user_login_curr", query, 100);
                userLastLogin = lll == null ? null :
                        lll.stream().collect(Collectors.toMap(a -> a.get("username").toString(), a -> Long.parseLong(a.get("loginDate").toString())));
            }
            for (Map<String, Object> a : list) {
                mer(a, userIntegralTOMap, userLastLogin);
            }
        }

//        for ()

//        if (userLastLogin != null && list != null) {
//            for (int i = 0; i < list.size(); i++) {
//                String username = list.get(i).get("username").toString();
//                list.get(i).put("lastLogin", userLastLogin.containsKey(username) ? userLastLogin.get(username) : null);
//            }
//        }
        //  导出
//        if (null != isExport && isExport - 1 == 0) {
//            String downloadUrl = exportExcel(list);
//            if (ValidateUtil.isNotNull(downloadUrl)) {
//                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
//                return AjaxResult.successResult(url);
//            }
//        }

        return successResult(list, total);
    }


    private void mer(Map<String, Object> data, Map<String, UserIntegralTO> userIntegralTOMap, Map<String, Long> userLastLogin) {
        if (data == null) {
            return;
        }
        String username = data.get("username").toString();
        if (data.get("signPhoto") != null && ValidateUtil.isNotNull(data.get("signPhoto").toString()) && ValidateUtil.isNotNetUrl(data.get("signPhoto").toString())) {
            data.put("icon", SYS_CONFIG_PATH.IMAGE_BASE_URL + data.get("signPhoto").toString());
        }
        data.put("totalIntegral", 0);
        data.put("integral", 0);
        if (userIntegralTOMap != null) {
            UserIntegralTO to = userIntegralTOMap.get(username);
            if (to != null) {
                data.put("totalIntegral", to.getTotalIntegral() == null ? to.getTotalIntegral() : 0);
                data.put("integral", to.getIntegral() == null ? to.getIntegral() : 0);
            }
        }

        if (userLastLogin != null) {
            Long aLong = userLastLogin.get(username);
            data.put("lastLogin", aLong);
        }
    }


    /**
     * 统计用户分析
     *
     * @param token
     * @param params
     * @param orderBy   排序
     * @param orderType 1-由高到低   2-由低到高
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(method = RequestMethod.POST, value = "admin/user/records/analy/statistics")
    @ResponseBody
    public String userAnalyStatis(String token, String params, String province, String county, String city, Long
            startTime, Long endTime, String orderBy, Integer orderType, Integer page, Integer size, Integer isExport) {

        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        //  只有城代可查看
        if (user.getUserType() != UserType.ADMIN && user.getUserType() != UserType.SUPER_ADMIN) {
            return successResult(null, 0);
        }
        if (startTime == null || startTime.equals("")) {
            startTime = DateUtils.getFirstDayOfMonth(new Date()).getTime();
        }
        if (endTime == null || startTime.equals("")) {
            endTime = new Date().getTime();
        }
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 12;
        }

        if (null != SYS_SETTINGS.HAS_MULTILINGUAL && 0 == 1 - SYS_SETTINGS.HAS_MULTILINGUAL) {//多语言版
            if (ValidateUtil.isNotNull(city)) {
                city = JYPinyinHelper.isChinese(city) ? JYPinyinHelper.toHanyuPinyin(city) : city.toLowerCase();
            }
        }

        /*
            SELECT si.*,COUNT(ofo.id) orderCount,COUNT(DISTINCT ofo.username) userCount,
            IFNULL(b.newUserCount,0) newUserCount,IFNULL(c.buyUserCount,0) AS buyUserCount
            FROM shop_info AS si
            LEFT JOIN order_info ofo ON ofo.shopId = si.id AND ofo.orderStatus = 9 AND ofo.isRefundApply = 0 AND ofo.isPartCancelApply = 0
            LEFT JOIN (
                SELECT COUNT(*) newUserCount,a.shopId FROM (
                    SELECT COUNT(oo.id) orderCount,SUM(CASE WHEN oo.createTime>1562573793796 THEN 1 ELSE 0 END) newCount,oo.username,shopId
                    FROM order_info oo WHERE oo.orderStatus = 9 AND oo.isRefundApply = 0 AND oo.isPartCancelApply = 0
                    GROUP BY oo.username,oo.shopId HAVING newCount>=orderCount
                )a GROUP BY a.shopId
            ) b ON b.shopId=ofo.shopId
            LEFT JOIN(
                SELECT COUNT(DISTINCT a.username) buyUserCount,a.shopId FROM(
                    SELECT COUNT(oo.id) orderCount,oo.username,oo.shopid
                    FROM order_info oo WHERE oo.orderStatus = 9 AND oo.isRefundApply = 0 AND oo.isPartCancelApply = 0
                    GROUP BY oo.username,oo.shopId
                    HAVING orderCount>1
                )a GROUP BY a.shopId
            ) c ON c.shopId=ofo.shopId
            WHERE ofo.shopId>0 AND si.delFlag = 0
            GROUP BY si.id
         */
        String sql = "SELECT si.*,COUNT(ofo.id) orderCount,COUNT(DISTINCT ofo.username) userCount, " +
                "IFNULL(b.newUserCount,0) newUserCount,IFNULL(c.buyUserCount,0) AS buyUserCount " +
                "FROM shop_info AS si   " +
                "LEFT JOIN order_info ofo ON ofo.shopId = si.id AND ofo.orderStatus = 9 AND ofo.isRefundApply = 0 AND ofo.isPartCancelApply = 0 " +
                "LEFT JOIN ( " +
                " SELECT COUNT(*) newUserCount,a.shopId FROM ( " +
                "  SELECT COUNT(oo.id) orderCount,SUM(CASE WHEN oo.createTime>" + (System.currentTimeMillis() - 60 * 24 * 60 * 60 * 1000L) + " THEN 1 ELSE 0 END) newCount,oo.username,shopId " +
                "  FROM order_info oo WHERE oo.orderStatus = 9 AND oo.isRefundApply = 0 AND oo.isPartCancelApply = 0  " +
                "  GROUP BY oo.username,oo.shopId HAVING newCount>=orderCount " +
                " )a GROUP BY a.shopId " +
                ") b ON b.shopId=ofo.shopId " +
                "LEFT JOIN( " +
                " SELECT COUNT(DISTINCT a.username) buyUserCount,a.shopId FROM( " +
                "  SELECT COUNT(oo.id) orderCount,oo.username,oo.shopid " +
                "  FROM order_info oo WHERE oo.orderStatus = 9 AND oo.isRefundApply = 0 AND oo.isPartCancelApply = 0  " +
                "  GROUP BY oo.username,oo.shopId  " +
                "  HAVING orderCount>1 " +
                " )a GROUP BY a.shopId " +
                ") c ON c.shopId=ofo.shopId " +
                "WHERE ofo.shopId>0 AND si.delFlag = 0 " +
                (StringUtils.isNotEmpty(province) ? (" AND si.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND si.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND si.county='" + county + "'") : "") +
                "AND si.isIntegralShop !=1 " +
//                " AND si.createTime BETWEEN " + startTime + " AND " + endTime +
                " GROUP BY si.id" +
                " ORDER BY " + (StringUtils.isNotEmpty(orderBy) ? orderBy : "userCount ") + "" +
                (orderType == null || orderType == 1 ? " DESC" : " ASC") +
                " LIMIT " + ((page - 1) * size) + "," + size;

        String sqlCount = "SELECT COUNT(DISTINCT si.id) as counts " +
                "FROM shop_info AS si   " +
                "LEFT JOIN order_info ofo ON ofo.shopId = si.id AND ofo.orderStatus = 9 AND ofo.isRefundApply = 0 AND ofo.isPartCancelApply = 0 " +
                "LEFT JOIN ( " +
                " SELECT COUNT(*) newUserCount,a.shopId FROM ( " +
                "  SELECT COUNT(oo.id) orderCount,SUM(CASE WHEN oo.createTime>" + (System.currentTimeMillis() - 60 * 24 * 60 * 60 * 1000L) + " THEN 1 ELSE 0 END) newCount,oo.username,shopId " +
                "  FROM order_info oo WHERE oo.orderStatus = 9 AND oo.isRefundApply = 0 AND oo.isPartCancelApply = 0  " +
                "  GROUP BY oo.username,oo.shopId HAVING newCount>=orderCount " +
                " )a GROUP BY a.shopId " +
                ") b ON b.shopId=ofo.shopId " +
                "LEFT JOIN( " +
                " SELECT COUNT(DISTINCT a.username) buyUserCount,a.shopId FROM( " +
                "  SELECT COUNT(oo.id) orderCount,oo.username,oo.shopid " +
                "  FROM order_info oo WHERE oo.orderStatus = 9 AND oo.isRefundApply = 0 AND oo.isPartCancelApply = 0  " +
                "  GROUP BY oo.username,oo.shopId  " +
                "  HAVING orderCount>1 " +
                " )a GROUP BY a.shopId " +
                ") c ON c.shopId=ofo.shopId " +
                "WHERE ofo.shopId>0 AND si.delFlag = 0 " +
                (StringUtils.isNotEmpty(province) ? (" AND si.province='" + province + "'") : "") +
                (StringUtils.isNotEmpty(city) ? (" AND si.city='" + city + "'") : "") +
                (StringUtils.isNotEmpty(county) ? (" AND si.county='" + county + "'") : "")
                + " AND si.isIntegralShop !=1 ";
//                " AND si.createTime BETWEEN " + startTime + " AND " + endTime;

        long total = 0L;
        List<HashMap<String, Object>> list = null;
        try {
            total = UserDaoSql.getInstance().getCount(sqlCount);
            list = UserDaoSql.getInstance().queryBySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (list != null && !list.isEmpty()) {
            return successResult(list, total);
        }
        //  导出
//        if (null != isExport && isExport - 1 == 0) {
//            String downloadUrl = exportExcel(list);
//            if (ValidateUtil.isNotNull(downloadUrl)) {
//                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
//                return AjaxResult.successResult(url);
//            }
//        }

        return successResult(null, 0);
    }
}
